import paho.mqtt.client as mqtt
import json
import pymysql
from datetime import datetime
import threading
import queue
import os
import time

# ------------------------ 数据库管理 ------------------------
class DatabaseManager:
    """数据库批量操作（支持UPSERT）"""
    def __init__(self, host, user, password, database):
        self.conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4'
        )
        self.table_columns = self._load_table_structure()
    
    def _load_table_structure(self):
        """动态获取表字段[5](@ref)"""
        with self.conn.cursor() as cursor:
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'map_device'
                ORDER BY ORDINAL_POSITION
            """)
            return [row[0] for row in cursor.fetchall()]
    
    def batch_upsert(self, data: dict):
        try:
            with self.conn.cursor() as cursor:
                # 去重处理：确保每个字段只出现一次
                filtered_data = {
                    k: v for k, v in data.items() 
                    if k in self.table_columns
                }
                
                # 构造字段列表（排除重复）
                columns = list(filtered_data.keys())
                values = list(filtered_data.values())
                
                # 生成更新语句（排除主键）
                update_clause = [
                    f"{col}=COALESCE(VALUES({col}), NULL)"
                    for col in columns
                    if col != 'device_id'  # 主键不更新
                ]
                
                sql = f"""
                    INSERT INTO map_device ({', '.join(columns)})
                    VALUES ({', '.join(['%s']*len(columns))})
                    ON DUPLICATE KEY UPDATE
                    {', '.join(update_clause)}
                """
                
                cursor.execute(sql, values)
                self.conn.commit()
                return True
        except Exception as e:
            self.conn.rollback()
            raise e
# ------------------------ MQTT 客户端 ------------------------
class MQTTClient:
    def __init__(self, host, port, client_id, username, password):
        self.client = mqtt.Client(client_id=client_id)
        self.client.username_pw_set(username, password)
        self.client.on_connect = self._on_connect
        self.client.on_message = self._on_message
        
        # 启动异步连接
        self.client.connect_async(host, port)
        self.client.loop_start()
        
        # 数据处理队列
        self.process_queue = queue.Queue()
        threading.Thread(target=self._db_worker, daemon=True).start()
    
    def _on_connect(self, client, userdata, flags, rc):
        if rc == 0:
            client.subscribe("gw/devices/coordinate")
        else:
            print(f"连接失败，错误码: {rc}")
    
    def _on_message(self, client, userdata, msg):
        try:
            payload = json.loads(msg.payload)
            data = {
                'device_id': payload.get('id'),
                'x': payload['data'].get('lng'),
                'y': payload['data'].get('lat'),
                'memo': payload['data'].get('soc'),
                'update_time': payload['data'].get('Upload_time')
            }
            print(data)
            self.process_queue.put(data)
        except Exception as e:
            print(f"消息解析失败: {str(e)}")
    
    def _db_worker(self):
        """数据库写入线程"""
        db = DatabaseManager(
            host="192.168.3.167",
            user="tester",
            password="tester1234",
            database="gwzaHTsoc"
        )
        
        while True:
            data = self.process_queue.get()
            try:
                if db.batch_upsert(data):
                    print(f"成功写入设备: {data['device_id']}")
            except Exception as e:
                print(f"数据库操作失败: {str(e)}")

# ------------------------ 主程序 ------------------------
if __name__ == "__main__":
    # 初始化MQTT客户端
    mqtt_client = MQTTClient(
        host="218.108.8.218",
        port=1883,
        client_id="LYM202503041005",
        username="gwzauser",
        password="gwza@2024"
    )
    
    # 保持主线程运行
    while True:
        time.sleep(1)